package net.huashitong.oversee.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.StringUtils;
import jodd.util.StringUtil;
import net.huashitong.oversee.databean.DepartRank;
import net.huashitong.oversee.databean.ProjectScoreRankDataBean;
import net.huashitong.oversee.entity.GradePoint;
import net.huashitong.oversee.filter.GradePointFilter;
import net.huashitong.oversee.filter.ScoreSummaryFilter;
import net.huashitong.user.entity.BnpUser;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

import static net.huashitong.utils.UtilsTools.getCurrentDateTime1;
import static net.huashitong.utils.UtilsTools.getCurrentDateTime2;

@Repository
public class GradePointDao extends StringDao<GradePoint> {

    public List<GradePoint> queryAllGradePoint(GradePointFilter filter) {
        GenericQuery query = createQuery("obj");
        //查询所有绩效考核
        return query.listResult(filter);
    }


    public List<GradePoint> queryAllValidGradePoint() {
        GenericQuery query = createQuery("obj");
        query.append(" and (invalid IS NULL or invalid = '')");
        //查询所有绩效考核
        return query.listResult();
    }


    public List<GradePoint> queryGradePoint(GradePointFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getCheckObject())) {
            query.append(" and checkObject like:checkObject").setParam("checkObject",
                    StringUtils.getLikewords(filter.getCheckObject()));
        }
        if (StringUtils.isNotBlank(filter.getCheckType())) {
            query.append(" and checkType like:checkType").setParam("checkType",
                    StringUtils.getLikewords(filter.getCheckType()));
        }
        if (StringUtils.isNotBlank(filter.getSoucre())) {
            query.append(" and soucre =:soucre").setParam("soucre",
                    filter.getSoucre());
        }
        if (StringUtils.isNotBlank(filter.getTaskContent())) {
            query.append(" and taskContent like:taskContent").setParam("taskContent",
                    StringUtils.getLikewords(filter.getTaskContent()));
        }
        if (StringUtils.isNotBlank(filter.getTaskId())) {
            query.append(" and taskId =:taskId").setParam("taskId",
                    filter.getTaskId());
        }
        if (StringUtils.isNotBlank(filter.getTaskName())) {
            query.append(" and taskName like:taskName").setParam("taskName",
                    StringUtils.getLikewords(filter.getTaskName()));
        }
        if (StringUtils.isNotBlank(filter.getInvalid())) {
            if ("1".equals(filter.getInvalid())) {
                query.append(" and invalid =:invalid").setParam("invalid",
                        "1");
            }
            if ("0".equals(filter.getInvalid())) {
                query.append(" and (invalid IS NULL or invalid = '')");
            }
        } else {
            //默认查询有效得分
            query.append(" and (invalid IS NULL or invalid = '')");
        }
        if (StringUtils.isNotBlank(filter.getCheckObjectId())) {
            query.append(" and checkObjectId =:checkObjectId").setParam("checkObjectId",
                    filter.getCheckObjectId());
        }

        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            query.append(" and (obj.taskName like:taskName or " +
                    "obj.checkObject like:checkObject or " +
                    "obj.taskContent like:taskContent or " +
                    "obj.taskId like:taskId or " +
                    "obj.resDept like:resDept) ").setParam("taskName",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("checkObject",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("taskContent",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("taskId",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resDept",
                    StringUtils.getLikewords(filter.getSearchWord()));
        }

        if (StringUtils.isNotBlank(filter.getStartDate())) {
            if (StringUtils.isNotBlank(filter.getEndDate())) {
                query.append(" and unix_timestamp( createDate ) between unix_timestamp( '" + filter.getStartDate() + "') and unix_timestamp( '" + filter.getEndDate() + "' )");
            } else {
                query.append(" and unix_timestamp( createDate ) between unix_timestamp( '" + filter.getStartDate() + "') and unix_timestamp( '" + getCurrentDateTime2() + "' )");
            }
        }
        if (filter.getPaging() != null) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
//                query.append(" ORDER BY  str_to_date( CONCAT(obj.updDate,' ',obj.updTime),'%Y-%m-%d %H:%i:%s') desc  ");
                filter.getPaging().setSortCol("createDate");
                filter.getPaging().setSortVal("desc");
            }
            if ("rank".equals(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("rank+0");
            }
            if ("soucre".equals(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("soucre+0");
            }
        } else {
            query.append(" ORDER BY createDate desc");
        }
        return query.listResult(filter);
    }

    /**
     * 根据Id  获取考核分
     *
     * @param id
     * @return
     */
    public List<GradePoint> getGradePointById(String id) {
        GenericQuery query = createQuery("obj");
        query.append("and obj.taskId=:taskId").setParam("taskId", id);
        return query.listResult();
    }

    /**
     * 根据多个id查询多个GradePoint
     *
     * @param ids
     * @return
     */
    public List<GradePoint> queryGradePointByIds(List<String> ids) {
        GenericQuery query = createQuery("obj");
        query.append("and obj.sysId in(:ids)").setParamList("ids", ids);
        return query.listResult();
    }

    /**
     * 根据考核类别查询
     *
     * @param checkType
     * @return
     */
    public List<GradePoint> queryGradePointByCheckType(String checkType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.checkType like:checkType").setParam("checkType",
                StringUtils.getLikewords(checkType));
        query.append(" and (invalid IS NULL or invalid = '')");

        return query.listResult();
    }

    public List<GradePoint> queryChecked() {
        StringBuffer sql = new StringBuffer();
        sql.append("select " +
                "g.check_object checkObject," +
                "g.check_type checkType," +
                "g.soucre soucre," +
                "g.task_content taskContent," +
                "g.task_id taskId," +
                "g.task_name taskName," +
                "g.object_id objectId," +
                "g.rank rank," +
                "g.res_dept resDept," +
                "g.type_rank typeRank," +
                "g.check_object_id checkObjectId " +
                "from t_gradepoint g, t_bnp_supervision_task t where " +
                "g.task_id = t.sys_id and t.aaa001 = '1' " +
                "and t.task_result in ('1','3','0','4') and (invalid is null or invalid = '') ");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(GradePoint.class));
        List<GradePoint> list = (List<GradePoint>) query.list();
        return list;
    }


    public List<GradePoint> queryCheckedInTime(ScoreSummaryFilter filter) {
        StringBuffer sql = new StringBuffer();
        sql.append("select " +
                "g.check_object checkObject," +
                "g.check_type checkType," +
                "g.soucre soucre," +
                "g.task_content taskContent," +
                "g.task_id taskId," +
                "g.task_name taskName," +
                "g.object_id objectId," +
                "g.rank rank," +
                "g.res_dept resDept," +
                "g.type_rank typeRank," +
                "g.check_object_id checkObjectId " +
                "from t_gradepoint g, t_bnp_supervision_task t where g.task_id = t.sys_id and t.aaa001 = '1' and t.task_result in ('1','3','0','4')");
        sql.append("and g.create_date between '" + filter.getStartTime() + "' and '" + filter.getEndTime() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(GradePoint.class));
        List<GradePoint> list = (List<GradePoint>) query.list();
        return list;
    }

    public void deletAllInvalidTaskGradePoint(List<String> taskIds) {
        StringBuffer hql = (new StringBuffer("delete from GradePoint obj where obj.taskId in(:taskIds)"));
        Query query = this.getSession().createQuery(hql.toString());
        query.setParameterList("taskIds", taskIds);
        query.executeUpdate();
    }

    /**
     * 查询未进入督查的绩效得分
     *
     * @return
     */
    public List<GradePoint> querySuccessfulGradePoint() {
        GenericQuery query = createQuery("obj");
        query.append("and obj.taskId like:taskId").setParam("taskId",
                StringUtils.getLikewords("sch_"));
        return query.listResult();
    }


    /**
     * 查询未进入督查的绩效得分（有时间段）
     *
     * @return
     */
    public List<GradePoint> querySuccessfulGradePointInTime(ScoreSummaryFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append("and obj.taskId like :taskId").setParam("taskId",
                StringUtils.getLikewords("sch_"));
        query.append(" and obj.createDate BETWEEN :startTime AND :endTime ").setParam("startTime",
                filter.getStartTime()).setParam("endTime",
                filter.getEndTime());
        return query.listResult();
    }

    public List<Map<String, Object>> getScoreNumCount(GradePointFilter filter) {
        StringBuffer sql = new StringBuffer();
        sql.append("select soucre,count(1) as count " +
                "from t_gradepoint g " +
                "where 1 = 1 ");
        if (StringUtils.isNotBlank(filter.getCheckObject())) {
            sql.append(" and g.check_object like " + "'" + StringUtils.getLikewords(filter.getCheckObject()) + "'");
        }
        if (StringUtils.isNotBlank(filter.getCheckType())) {
            sql.append(" and g.check_type = " + "'" + filter.getCheckType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getSoucre())) {
            sql.append(" and g.soucre = " + "'" + filter.getSoucre() + "'");
        }
        if (StringUtils.isNotBlank(filter.getTaskContent())) {
            sql.append(" and g.task_content like " + "'" + StringUtils.getLikewords(filter.getTaskContent()) + "'");
        }
        if (StringUtils.isNotBlank(filter.getTaskId())) {
            sql.append(" and g.task_id = " + "'" + filter.getTaskId() + "'");
        }
        if (StringUtils.isNotBlank(filter.getTaskName())) {
            sql.append(" and g.task_name like " + "'" + StringUtils.getLikewords(filter.getTaskName()) + "'");
        }
        if (StringUtils.isNotBlank(filter.getCheckObjectId())) {
            sql.append(" and g.check_object_id = " + "'" + filter.getCheckObjectId() + "'");
        }

        if (StringUtils.isNotBlank(filter.getInvalid())) {
            if ("1".equals(filter.getInvalid())) {
                sql.append(" and g.invalid = '1'");
            }
            if ("0".equals(filter.getInvalid())) {
                sql.append(" and (g.invalid IS NULL or g.invalid = '')");
            }
        } else {
            //默认查询有效得分
            sql.append(" and (g.invalid IS NULL or g.invalid = '')");
        }
        if (StringUtils.isNotBlank(filter.getStartDate())) {
            if (StringUtils.isNotBlank(filter.getEndDate())) {
                sql.append(" and unix_timestamp( g.create_date ) between unix_timestamp( '" + filter.getStartDate() + "') and unix_timestamp( '" + filter.getEndDate() + "' )");
            } else {
                sql.append(" and unix_timestamp( g.create_date ) between unix_timestamp( '" + filter.getStartDate() + "') and unix_timestamp( '" + getCurrentDateTime2() + "' )");
            }
        }

        sql.append(" group by g.soucre order by g.soucre+0 desc ");


        SQLQuery query = getSession().createSQLQuery(sql.toString());
        //返回map
        List<Map<String, Object>> list = (List) query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return list;
    }

    public List<GradePoint> queryByProjectId(String projectId) {
        StringBuffer sql = new StringBuffer();
        sql.append("" +
                "select g.sys_id as sysId, g.soucre as soucre,\n" +
                "g.task_name as taskName, g.task_id as taskId,\n" +
                "g.check_object_id as checkObjectId, g.check_type as checkType " +
                "from t_gradepoint g,t_bnp_supervision_task t " +
                "where g.task_id = t.sys_id \n" +
                " and g.check_type like '%经办人%' and t.aaa001 = '1' ");
        if (StringUtils.isNotBlank(projectId)) {
            sql.append("and t.project_id = '" + projectId + "'");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(GradePoint.class));
        List<GradePoint> result = query.list();
        return result;
    }


    public List<GradePoint> queryTackToTaskGradePointList(GradePointFilter filter) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(filter.getSysId())) {
            query.append("and obj.sysId=:sysId").setParam("sysId", filter.getSysId());
        }
        if (StringUtils.isNotBlank(filter.getCheckObjectId())) {
            query.append("and obj.checkObjectId=:checkObjectId").setParam("checkObjectId", filter.getCheckObjectId());
        }
        if (StringUtils.isNotBlank(filter.getCheckObject())) {
            query.append(" and checkObject like:checkObject").setParam("checkObject",
                    StringUtils.getLikewords(filter.getCheckObject()));
        }
        if (StringUtils.isNotBlank(filter.getCheckType())) {
            query.append(" and checkType like:checkType").setParam("checkType",
                    StringUtils.getLikewords(filter.getCheckType()));
        }
        return query.listResult();
    }

    public List<ProjectScoreRankDataBean> statisticalItemScore(String resPhone, String status) {
        StringBuffer sql = new StringBuffer();
        sql.append("select p.sys_id as projectId, p.name as projectName,avg(g.soucre) as score,count(g.soucre) as count from t_bnp_new_project p,\n" +
                "t_bnp_supervision_task t,t_gradepoint g\n" +
                "where p.sys_id = t.project_id and t.sys_id = g.task_id and check_type like '%经办人%' and t.aaa001 = '1' ");
        sql.append("and p.res_phone = '" + resPhone + "'");
        if(StringUtil.isNotBlank(status)){
            sql.append("and p.status = '" + status + "'");
        }
        sql.append("group by p.name,p.sys_id");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(ProjectScoreRankDataBean.class));
        List<ProjectScoreRankDataBean> result = query.list();
        return result;
    }
}
